package com.pegatroncorp.motion.resend;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;

import com.pegatron.motion.stub.Add;
import com.pegatron.motion.stub.AddOrUpdate;
import com.pegatron.motion.stub.Case;
import com.pegatron.motion.stub.CustomFieldValue;
import com.pegatron.motion.stub.CustomFieldValues;
import com.pegatron.motion.stub.CustomerHandle;
import com.pegatron.motion.stub.Estimate;
import com.pegatron.motion.stub.ItemLine;
import com.pegatron.motion.stub.ItemList;
import com.pegatron.motion.stub.ItemRef;
import com.pegatron.motion.stub.Name;
import com.pegatron.motion.stub.Request;
import com.pegatron.motion.stub.SalesPrice;
import com.pegatron.motion.stub.Smbxml;
import com.pegatron.motion.stub.StatusRef;

public class MarshalMotionNewTest {
//	private static String jdbc_url = "jdbc:oracle:thin:@192.168.108.164:1521:peuesdb1";// eu
	private static String jdbc_url = "jdbc:oracle:thin:@192.168.11.72:1521:pamesdb1";// am
	private static String username = "rr";// am
	private static String password = "repadm397";// am
	private static final String STUB_CONTEXT_PATH = "com.pegatron.motion.stub";

	private static Connection getConnection() {
		Connection conn = null;
		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			conn = DriverManager.getConnection(jdbc_url, username, password);
			System.out.println("JDBC: connected to \n" + (conn != null ? jdbc_url : "100") + "!\n");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("ERROR: failed to connect!\n" + e.toString());
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("ERROR: failed to connect!\n" + e.toString());
		}
		return conn;
	}

	private static void closeConnection(Connection connection, Statement statement, ResultSet result) {
		try {
			if (result != null) {
				result.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			result = null;
		} finally {
			try {
				if (result != null) {
					result.close();
				}
			} catch (Exception e) {
				result = null;
			}
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			statement = null;
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (Exception e) {
				statement = null;
			}
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			connection = null;
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
				connection = null;
			}
		}
	}

	public static boolean marshalRA(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append(" select a.rma_no,a.rma_type,b.customer_po case_number,b.customer_pn case_handle,to_char(a.issue_date,'MM/DD/YYYY')issue_date ");
			buf.append(" from rm_header a,rm_header_extrainfo b ");
			buf.append(" where a.delete_no is null ");
			buf.append(" and a.apply_no=b.apply_no and a.rma_center=b.rma_center ");
			buf.append(" and a.rma_center in (62,64) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='ACCEPT' and x.repair_result='MOTION_EDI_AUTO_CREATE' ");
			buf.append("  and x.entry_date>=to_date('2011/08/03','YYYY/MM/DD') ");
			buf.append("  and x.entry_date< to_date('2011/08/03','YYYY/MM/DD')+1 ");
			buf.append(" ) ");
			buf.append(" and a.rma_type not in ('W','RP','FM') ");
			buf.append(" order by b.customer_po ");
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalRA[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "RA", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "RA", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static boolean marshalRE(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append(" select a.rma_no,a.rma_type,b.customer_po case_number,b.customer_pn case_handle,to_char(b.receive_date,'MM/DD/YYYY')receive_date ");
			buf.append(" from rm_header a,rm_header_extrainfo b ");
			buf.append(" where a.delete_no is null ");
			buf.append(" and a.apply_no=b.apply_no and a.rma_center=b.rma_center ");
			buf.append(" and a.rma_center in (62,64) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='ACCEPT' and x.repair_result='MOTION_EDI_AUTO_CREATE' ");
			buf.append(" ) ");
			// buf.append(" and a.apply_no = '2088685' ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='RECEIVE' ");
			buf.append("  and x.entry_date>=to_date('2013/01/01','YYYY/MM/DD') ");
			// buf.append("  and x.entry_date< to_date('2011/08/03','YYYY/MM/DD')+1 ");
			buf.append(" ) ");
			buf.append(" and a.rma_type not in ('W','RP','FM') ");
			buf.append(" order by b.customer_po ");
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalRE[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "RE", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "RE", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static boolean marshalSH(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append(" select a.apply_no,a.rma_center,a.rma_no,a.rma_type,b.customer_po case_number,b.customer_pn case_handle, ");
			buf.append(" to_char(nvl((select max(x.entry_date) from rm_header_tran x where a.apply_no=x.apply_no and a.rma_center=x.rma_center and x.status='SHIP'),sysdate),'MM/DD/YYYY')ship_date, ");
			buf.append(" to_char(nvl((select max(x.entry_date) from rm_header_tran x where a.apply_no=x.apply_no and a.rma_center=x.rma_center and x.status='CLOSED'),sysdate),'MM/DD/YYYY')closed_date, ");
			buf.append(" (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='OUT' and rownum=1)track_no, ");
			buf.append(" to_char((select max(x.issue_date)    from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y'),'MM/DD/YYYY')quote_date, ");
			buf.append(" to_char((select max(x.response_date) from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y'),'MM/DD/YYYY')quote_approval_date, ");
			buf.append(" to_char((select max(x.finish_date)   from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y'),'MM/DD/YYYY')quote_repaired_date ");
			buf.append(" from rm_header a,rm_header_extrainfo b ");
			buf.append(" where a.delete_no is null ");
			buf.append(" and a.apply_no=b.apply_no and a.rma_center=b.rma_center ");
			buf.append(" and a.rma_center in (62,64) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='ACCEPT' and x.repair_result='MOTION_EDI_AUTO_CREATE' ");
			buf.append(" ) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='SHIP' ");
			buf.append("  and x.entry_date>=to_date('2012/11/05','YYYY/MM/DD') ");
			buf.append("  and x.entry_date< to_date('2012/11/05','YYYY/MM/DD')+1 ");
			buf.append(" ) ");
			buf.append(" and b.customer_po in ('453907-L9K5M') ");
			buf.append(" and a.rma_type not in ('W','RP','FM') ");
			buf.append(" order by b.customer_po ");
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalSH[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "SH", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "SH", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static boolean marshalQS(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append("SELECT a.order_no, ");
			buf.append("       c.item_no, ");
			buf.append("       a.rma_site, ");
			buf.append("       a.order_nr, ");
			buf.append("       a.order_type, ");
			buf.append("       d.customer_po case_number, ");
			buf.append("       d.customer_pn case_handle, ");
			buf.append("       b.mrb case_entity_handle, ");
			buf.append("       c.serial_nr, ");
			buf.append("       TO_CHAR ( ");
			buf.append("          (SELECT x.issue_date ");
			buf.append("             FROM ers_item_onhold x ");
			buf.append("            WHERE     x.order_no = c.order_no ");
			buf.append("                  AND x.item_no = c.item_no ");
			buf.append("                  AND x.rma_site = c.rma_site ");
			buf.append("                  AND ROWNUM = 1), ");
			buf.append("          'MM/DD/YYYY') ");
			buf.append("          issue_date ");
			buf.append("  FROM ers_order a, ers_order_hist b, ers_item c,ers_order_extra d ");
			buf.append(" WHERE     a.dt_id IS NULL ");
			buf.append("       AND c.dt_id IS NULL ");
			buf.append("       AND a.order_no = b.order_no ");
			buf.append("       AND a.rma_site = b.rma_site ");
			buf.append("       AND a.order_no = c.order_no ");
			buf.append("       AND a.rma_site = c.rma_site ");
			buf.append("       and a.order_no = d.order_no ");
			buf.append("       and a.rma_site = d.rma_site ");
			buf.append("       AND a.rma_site IN (62, 64) ");
			buf.append("       AND EXISTS ");
			buf.append("              (SELECT 1 ");
			buf.append("                 FROM ers_order_track x ");
			buf.append("                WHERE     x.order_no = a.order_no ");
			buf.append("                      AND x.rma_site = a.rma_site ");
			buf.append("                      AND x.status = 'ACCEPT' ");
			buf.append("                      AND x.memo = 'MOTION_EDI_AUTO_CREATE') ");
			buf.append(" and a.order_no in (168894) ");
			/*
			 * buf.append("       AND EXISTS ");
			 * buf.append("              (SELECT 1 ");
			 * buf.append("                 FROM ers_item_onhold x ");
			 * buf.append("                WHERE     x.order_no = c.order_no ");
			 * buf.append("                      AND x.rma_site = c.rma_site ");
			 * buf.append("                      AND x.item_no = c.item_no ");
			 * buf.append("                      AND x.issue_date >= ");
			 * buf.append
			 * ("                             TO_DATE ('2013/06/27', 'YYYY/MM/DD') "
			 * ); buf.append("                      AND x.issue_date < ");
			 * buf.append(
			 * "                             TO_DATE ('2013/07/04', 'YYYY/MM/DD') + 1) "
			 * );
			 */
			buf.append("       AND a.order_type NOT IN ('W', 'RP', 'FM') ");
//			buf.append("       AND a.order_type NOT IN ('W', 'FM') ");
			System.out.println(buf.toString());
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalQS[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "QS", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_5_01.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "QS", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_5_01.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static boolean marshalQA(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			// buf.append(" select a.case_number,a.case_handle,to_char(a.response_date,'MM/DD/YYYY')response_date ");
			// buf.append(" from rm_motion_onhold a ");
			// buf.append(" where a.rma_center in (62,64) ");
			// buf.append(" and a.response_date>=to_date('2011/08/03','YYYY/MM/DD') ");
			// buf.append(" and a.response_date< to_date('2011/08/03','YYYY/MM/DD')+1 ");
			// buf.append(" order by a.case_number ");
			buf.append(" SELECT a.*, (select sysdate from dual) response_date FROM ers_edi_motion_header a WHERE order_nr IN ('EJ350026', 'EJ350028', 'EJ350046', 'EJ350025') AND rma_site IN (62, 64)");
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalQA[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "QA", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "QA", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static boolean marshalCA(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append(" select a.rma_no,a.rma_type,b.customer_po case_number,b.customer_pn case_handle,to_char(a.issue_date,'MM/DD/YYYY')issue_date ");
			buf.append(" from rm_header a,rm_header_extrainfo b ");
			buf.append(" where a.delete_no is null ");
			buf.append(" and a.apply_no=b.apply_no and a.rma_center=b.rma_center ");
			buf.append(" and a.rma_center in (62,64) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='ACCEPT' and x.repair_result='MOTION_EDI_AUTO_CREATE' ");
			buf.append("  and x.entry_date>=to_date('2011/08/03','YYYY/MM/DD') ");
			buf.append("  and x.entry_date< to_date('2011/08/03','YYYY/MM/DD')+1 ");
			buf.append(" ) ");
			buf.append(" and a.rma_type in ('RP','FM') ");
			buf.append(" order by b.customer_po ");
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalCA[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "CA", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "CA", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static boolean marshalCS(String fileName) {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			// xml========================================
			JAXBContext jc = JAXBContext.newInstance(STUB_CONTEXT_PATH);
			Marshaller m = jc.createMarshaller();
			m.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
			m.setProperty(Marshaller.JAXB_FRAGMENT, Boolean.TRUE);
			// connection========================================
			conn = getConnection();
			StringBuffer buf = new StringBuffer();
			buf.append(" select a.apply_no,a.rma_center,a.rma_no,a.rma_type,b.customer_po case_number,b.customer_pn case_handle, ");
			buf.append(" to_char(nvl((select max(x.entry_date) from rm_header_tran x where a.apply_no=x.apply_no and a.rma_center=x.rma_center and x.status='SHIP'),sysdate),'MM/DD/YYYY')ship_date, ");
			buf.append(" to_char(nvl((select max(x.entry_date) from rm_header_tran x where a.apply_no=x.apply_no and a.rma_center=x.rma_center and x.status='CLOSED'),sysdate),'MM/DD/YYYY')closed_date, ");
			buf.append(" (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='OUT' and rownum=1)track_no, ");
			buf.append(" (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='IN'  and rownum=1)track_no_rtn, ");
			buf.append(" to_char((select max(x.issue_date)    from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y'),'MM/DD/YYYY')quote_date, ");
			buf.append(" to_char((select max(x.response_date) from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y'),'MM/DD/YYYY')quote_approval_date, ");
			buf.append(" to_char((select max(x.finish_date)   from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y'),'MM/DD/YYYY')quote_repaired_date ");
			buf.append(" from rm_header a,rm_header_extrainfo b ");
			buf.append(" where a.delete_no is null ");
			buf.append(" and a.apply_no=b.apply_no and a.rma_center=b.rma_center ");
			buf.append(" and a.rma_center in (62,64) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='ACCEPT' and x.repair_result='MOTION_EDI_AUTO_CREATE' ");
			buf.append(" ) ");
			buf.append(" and exists( ");
			buf.append("  select 1 from rm_header_tran x ");
			buf.append("  where x.apply_no=a.apply_no and x.rma_center=a.rma_center ");
			buf.append("  and x.status='SHIP' ");
			buf.append("  and x.entry_date>=to_date('2011/08/03','YYYY/MM/DD') ");
			buf.append("  and x.entry_date< to_date('2011/08/03','YYYY/MM/DD')+1 ");
			buf.append(" ) ");
			buf.append(" and a.rma_type in ('RP','FM') ");
			buf.append(" order by b.customer_po ");
			statement = conn.prepareStatement(buf.toString());
			rs = statement.executeQuery();
			int i = 0;
			while (rs.next()) {
				i++;
				File file = new File(fileName);
				System.out.println("marshalCS[" + i + "], fileName=" + fileName + ", file.length()=" + file.length());
				FileWriter fw;
				if (file.length() == 0) {
					Smbxml smb = new Smbxml();
					smb.setMissingFieldOverwrite("false");
					smb = processMessage(smb, "CS", rs);

					fw = new FileWriter(file, true);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				} else {
					Unmarshaller u = jc.createUnmarshaller();
					Smbxml smb = (Smbxml) u.unmarshal(new FileInputStream(fileName));
					smb = processMessage(smb, "CS", rs);

					fw = new FileWriter(file, false);
					fw.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
					fw.write("<!DOCTYPE smbxml SYSTEM \"http://www.netsuite.com/xml/dtd/smb_4_13.dtd\">\n");
					m.marshal(smb, fw);
					fw.close();
				}
			}
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JAXBException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConnection(conn, statement, rs);
		}
		return false;
	}

	public static Smbxml processMessage(Smbxml smb, String key, ResultSet rs) throws Exception {
		if (key.equals("RA")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("false");
			AddOrUpdate addOrUpdate = new AddOrUpdate();
			Case case_ = new Case();
			case_.setHandle(rs.getString("case_handle"));
			case_.setCaseNumber(rs.getString("case_number"));

			CustomFieldValues customFieldValues = new CustomFieldValues();

			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Request Received Date");
			customFieldValue.setvalue(rs.getString("issue_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Request Received Status");
			customFieldValue.setvalue("REQ_RECEIVED");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData03");
			customFieldValue.setvalue("ACCEPTED");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData04");
			customFieldValue.setvalue("The selected unit has been processed.");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData05");
			customFieldValue.setvalue("N/A");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData06");
			customFieldValue.setvalue("N/A");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			case_.setCustomFieldValues(customFieldValues);

			addOrUpdate
					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
					.add(case_);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
			smb.getHeaderOrRequestOrResponse().add(request);
			return smb;
		} else if (key.equals("RE")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("false");
			AddOrUpdate addOrUpdate = new AddOrUpdate();
			Case case_ = new Case();
			case_.setHandle(rs.getString("case_handle"));
			case_.setCaseNumber(rs.getString("case_number"));

			CustomFieldValues customFieldValues = new CustomFieldValues();

			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Product Received Date");
			customFieldValue.setvalue(rs.getString("receive_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			case_.setCustomFieldValues(customFieldValues);

			addOrUpdate
					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
					.add(case_);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
			smb.getHeaderOrRequestOrResponse().add(request);
			return smb;
		} else if (key.equals("SH")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("false");
			AddOrUpdate addOrUpdate = new AddOrUpdate();
			Case case_ = new Case();
			case_.setHandle(rs.getString("case_handle"));
			case_.setCaseNumber(rs.getString("case_number"));

			CustomFieldValues customFieldValues = new CustomFieldValues();

			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Product Shipped");
			customFieldValue.setvalue(rs.getString("ship_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("RMA Completed Date");
			customFieldValue.setvalue(rs.getString("closed_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Shipment Tracking #");
			customFieldValue.setvalue(rs.getString("track_no"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Quote Date");
			customFieldValue.setvalue(rs.getString("quote_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("QuoteApprovalDate");
			customFieldValue.setvalue(rs.getString("quote_approval_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Quote Repaired Date");
			customFieldValue.setvalue(rs.getString("quote_repaired_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			// .......
			Connection conn1 = null;
			PreparedStatement statement1 = null;
			ResultSet rs1 = null;
			try {
				// connection========================================
				conn1 = getConnection();
				StringBuffer buf = new StringBuffer();
				buf.append(" select distinct m.apply_no,m.rma_center,m.rma_type,m.case_handle,m.case_number,m.case_entity_handle,m.keyin_date,m.quote_date,m.quote_approval_date,m.track_no,m.track_no_rtn,");
				buf.append(" o.new_part_no part_no,");
				buf.append(" (select x.short_desc from wm_part_master x where x.part_no=o.new_part_no)part_desc,");
				buf.append(" o.problem_code,");
				buf.append(" (RMA_UTIL_P.GETPROBLEMDESC(o.APPLY_NO, o.LINE_NO, o.RMA_CENTER, o.CODE_GROUP, o.PROBLEM_CODE))problem_desc,");
				buf.append(" decode(elm_order.getPrice4Motion(o.new_part_no),-1,0,elm_order.getPrice4Motion(o.new_part_no))+108.7 price");
				buf.append(" from(select a.apply_no,a.rma_center,a.rma_type,");
				buf.append("  b.case_handle,b.case_number,b.case_entity_handle,");
				buf.append("  (select max(x.entry_date) from rm_header_tran x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.status='RECEIVE')keyin_date,");
				buf.append("  (select max(x.issue_date)    from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y')quote_date,");
				buf.append("  (select max(x.response_date) from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y')quote_approval_date,");
				buf.append("  (select max(x.finish_date)   from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y')quote_repaired_date,");
				buf.append("  (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='OUT' and rownum=1)track_no,");
				buf.append("  (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='IN' and rownum=1)track_no_rtn");
				buf.append("  from rm_header a,rm_motion_header b");
				buf.append("  where a.delete_no is null");
				buf.append("  and a.apply_no=b.apply_no and a.rma_center=b.rma_center");
				buf.append("  and a.rma_center=" + rs.getString("rma_center"));
				buf.append("  and a.apply_no=" + rs.getString("apply_no"));
				buf.append(" )m,rm_item_detail n,rm_repair_detail o");
				buf.append(" where n.delete_no is null");
				buf.append(" and m.apply_no=n.apply_no and m.rma_center=n.rma_center");
				buf.append(" and n.apply_no=o.apply_no and n.rma_center=o.rma_center and n.line_no=o.line_no");
				buf.append(" and (o.new_part_no is not null or (o.status='REPAIR' and o.problem_code is not null))");

				statement1 = conn1.prepareStatement(buf.toString());
				rs1 = statement1.executeQuery();
				int i = 0;
				while (rs1.next()) {
					i++;
					String num = (i <= 9) ? "0" + (i) : "" + (i);
					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("rd_partnum" + num);
					customFieldValue.setvalue(rs1.getString("part_no"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);

					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("rd_desc" + num);
					customFieldValue.setvalue(rs1.getString("part_desc"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);

					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("rd_FailureCode" + num);
					customFieldValue.setvalue(rs1.getString("problem_code"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);

					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("rd_pegatron_failure" + num);
					customFieldValue.setvalue(rs1.getString("problem_code"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);

					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("rd_TechComment" + num);
					customFieldValue.setvalue(rs1.getString("problem_desc"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);

					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("rd_price" + num);
					customFieldValue.setvalue("0");
					customFieldValues.getCustomFieldValue().add(customFieldValue);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				closeConnection(conn1, statement1, rs1);
			}

			case_.setCustomFieldValues(customFieldValues);

			addOrUpdate
					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
					.add(case_);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
			smb.getHeaderOrRequestOrResponse().add(request);

			return smb;
		} else if (key.equals("QS")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("true");
			Add add = new Add();
			Estimate estimate = new Estimate();
			StatusRef statusRef = new StatusRef();
			Name name = new Name();
			name.setvalue("Open - New Quote Needs Review");
			statusRef.setName(name);
			estimate.setStatusRef(statusRef);

			estimate.setTranId(rs.getString("case_number"));

			CustomerHandle customerHandle = new CustomerHandle();
			customerHandle.setHandle(rs.getString("case_entity_handle"));
			estimate.getCustomerHandleOrCustomerRef().add(customerHandle);

			estimate.setTranDate(rs.getString("issue_date"));

			CustomFieldValues customFieldValues = new CustomFieldValues();
			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Serial Number");
			customFieldValue.setvalue(rs.getString("serial_nr"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			estimate.setCustomFieldValues(customFieldValues);

			ItemList itemList = new ItemList();

			// .......
			Connection conn1 = null;
			PreparedStatement statement1 = null;
			ResultSet rs1 = null;
			try {
				// connection========================================
				conn1 = getConnection();
				StringBuffer buf = new StringBuffer();
				/*
				 * Formatted on 2013/07/08 �W�� 09:27:03 (QP5
				 * v5.149.1003.31008)
				 */
				buf.append("SELECT m.case_handle, ");
				buf.append("       m.case_number, ");
				buf.append("       m.case_entity_handle, ");
				buf.append("       m.order_no, ");
				buf.append("       m.item_no, ");
				buf.append("       m.rma_site, ");
				buf.append("       m.status, ");
				buf.append("       m.order_type, ");
				buf.append("       m.part_nr, ");
				buf.append("       m.part_desc, ");
				buf.append("       m.serial_nr, ");
				buf.append("       m.reason_code, ");
				buf.append("       (SELECT x.reason_name ");
				buf.append("          FROM ers_cf_reason x ");
				buf.append("         WHERE x.reason_id = m.reason_code) ");
				buf.append("          reason, ");
				buf.append("       m.problem_code, ");
				buf.append("       (rma_util_p.GETPROBLEMDESC (m.order_no, ");
				buf.append("                                   m.item_NO, ");
				buf.append("                                   m.RMA_site, ");
				buf.append("                                   TO_NUMBER (m.code_group), ");
				buf.append("                                   m.PROBLEM_CODE)) ");
				buf.append("          problem, ");
				buf.append("       m.code_group, ");
				buf.append("       m.qty, ");
				buf.append("       m.total_price, ");
				buf.append("       m.material_price, ");
				buf.append("       m.labor_price, ");
				buf.append("       m.customer, ");
				buf.append("       m.keyin_date, ");
				buf.append("       m.warranty, ");
				buf.append("       m.guid, ");
				buf.append("       m.finish, ");
				buf.append("       m.response_date, ");
				buf.append("       m.response_result ");
				buf.append("  FROM (SELECT d.case_handle, ");
				buf.append("               d.case_number, ");
				buf.append("               d.case_entity_handle, ");
				buf.append("               b.order_no, ");
				buf.append("               b.item_no, ");
				buf.append("               b.rma_site, ");
				buf.append("               b.process_no, ");
				buf.append("               e.status, ");
				buf.append("               g.order_type, ");
				buf.append("               (SELECT x.serial_nr ");
				buf.append("                  FROM ers_item_track x ");
				buf.append("                 WHERE     x.order_no = b.order_no ");
				buf.append("                       AND x.item_no = b.item_no ");
				buf.append("                       AND x.rma_site = b.rma_site ");
				buf.append("                       AND x.track_no = 1) ");
				buf.append("                  serial_nr, ");
				buf.append("               (SELECT x.short_desc ");
				buf.append("                  FROM wm_part_master x ");
				buf.append("                 WHERE x.part_no = f.part_nr) ");
				buf.append("                  part_desc, ");
				buf.append("               f.part_nr, ");
				// buf.append("               c.code_group, ");
				buf.append("               (SELECT CODE_GROUP ");
				buf.append("                  FROM RM_BASE_DEFINE x ");
				buf.append("                 WHERE     x.RMA_CENTER = g.rma_site ");
				buf.append("                       AND x.PRODUCT_TYPE = h.product_type ");
				buf.append("                       AND x.RMA_TYPE = g.order_type ");
				buf.append("                       AND x.CUSTOMER_TYPE = ");
				buf.append("                              ers_util_p.getcustomertype (g.customer)) ");
				buf.append("                  code_group, ");
				buf.append("               c.problem_code, ");
				buf.append("               c.reason_code, ");
				buf.append("               (SELECT x.keyin_date ");
				buf.append("                  FROM ers_item_summary x ");
				buf.append("                 WHERE x.item_no = b.item_no AND x.rma_site = b.rma_site) ");
				buf.append("                  keyin_date, ");
				buf.append("               TO_DATE ( ");
				buf.append("                  (SELECT x.custom_svc_center_data02 ");
				buf.append("                     FROM ers_edi_motion_header x ");
				buf.append("                    WHERE     x.order_no = b.order_no ");
				buf.append("                          AND x.rma_site = b.rma_site ");
				buf.append("                          AND ROWNUM = 1), ");
				buf.append("                  'MM/DD/YYYY') ");
				buf.append("                  warranty, ");
				buf.append("               1 qty, ");
				buf.append("               DECODE (elm_order.getPrice4Motion (f.part_nr), ");
				buf.append("                       -1, 0, ");
				buf.append("                       elm_order.getPrice4Motion (f.part_nr)) ");
				buf.append("               + 108.7 ");
				buf.append("                  total_price, ");
				buf.append("               DECODE (elm_order.getPrice4Motion (f.part_nr), ");
				buf.append("                       -1, 0, ");
				buf.append("                       elm_order.getPrice4Motion (f.part_nr)) ");
				buf.append("                  material_price, ");
				buf.append("               108.7 labor_price, ");
				buf.append("               g.customer, ");
				buf.append("               a.guid, ");
				buf.append("               a.finish, ");
				buf.append("               b.RESPONSE_RESULT, ");
				buf.append("               a.response_date ");
				buf.append("          FROM ers_item_onhold a, ");
				buf.append("               ers_item_onhold_detail b, ");
				buf.append("               ers_process c, ");
				buf.append("               ers_edi_motion_header d, ");
				buf.append("               ers_mtc_order e, ");
				buf.append("               ers_mtc_order_item f, ");
				buf.append("               ers_order g, ");
				buf.append("               ers_item h ");
				buf.append("         WHERE     a.guid = B.GUID ");
				buf.append("               AND a.order_no = b.order_no ");
				buf.append("               AND a.item_no = b.item_no ");
				buf.append("               AND a.rma_site = b.rma_site ");
				buf.append("               AND b.item_no = c.item_no ");
				buf.append("               AND b.rma_site = c.rma_site ");
				buf.append("               AND b.process_no = c.process_no ");
				buf.append("               AND a.order_no = D.ORDER_NO ");
				buf.append("               AND a.rma_site = d.rma_site ");
				buf.append("               AND b.order_no = E.APPLY_ORDER_NO ");
				buf.append("               AND b.item_no = e.apply_item_no ");
				buf.append("               AND B.PROCESS_NO = E.APPLY_PROCESS_NO ");
				buf.append("               AND E.MTC_ORDER_NO = f.mtc_order_no ");
				buf.append("               AND a.order_no = g.order_no ");
				buf.append("               AND g.order_no = h.order_no ");
				buf.append("               AND g.rma_site = h.rma_site ");
				buf.append("               AND h.item_no = a.item_no ");
				buf.append("               AND a.order_no = " + rs.getString("order_no") + " ");
				buf.append("               AND a.item_no = " + rs.getString("item_no") + " ");
				buf.append("               AND a.rma_site = " + rs.getString("rma_site") + " ");
				buf.append("               AND a.finish = 'N') m ");
				buf.append(" WHERE m.order_type = 'R' ");
				// buf.append("       AND (TRUNC (m.keyin_date) > warranty OR m.reason_code = 'C02') ");
				statement1 = conn1.prepareStatement(buf.toString());
				rs1 = statement1.executeQuery();
				int i = 0;
				while (rs1.next()) {
					i++;
					ItemLine itemLine = new ItemLine();
					ItemRef itemRef = new ItemRef();
					Name name1 = new Name();
					name1.setvalue("CID Part");
					itemRef.setName(name1);
					itemLine.getItemHandleOrItemRef().add(itemRef);

					itemLine.setQuantity(rs1.getString("qty"));

					SalesPrice salesPrice = new SalesPrice();
					salesPrice.setvalue(rs1.getString("total_price"));
					itemLine.getSalesPriceOrAmount().add(salesPrice);
					itemLine.setDescription(rs1.getString("part_desc"));

					CustomFieldValues itemCustomFieldValues = new CustomFieldValues();

					CustomFieldValue itemCustomFieldValue = new CustomFieldValue();
					itemCustomFieldValue.setName("Part Used");
					itemCustomFieldValue.setvalue(rs1.getString("part_nr"));
					itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

					itemCustomFieldValue = new CustomFieldValue();
					itemCustomFieldValue.setName("CID Material Price");
					itemCustomFieldValue.setvalue(rs1.getString("material_price"));
					itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

					itemCustomFieldValue = new CustomFieldValue();
					itemCustomFieldValue.setName("CID Labor Price");
					if (i == 1) {
						itemCustomFieldValue.setvalue(rs1.getString("labor_price"));
					} else {
						itemCustomFieldValue.setvalue("0");
					}
					itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

					itemCustomFieldValue = new CustomFieldValue();
					itemCustomFieldValue.setName("CID Quote Reason");
					itemCustomFieldValue.setvalue(rs1.getString("reason"));
					itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

					itemCustomFieldValue = new CustomFieldValue();
					itemCustomFieldValue.setName("ServiceCenterQuoteData01");
					itemCustomFieldValue.setvalue("Quoting unit");
					itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

					itemCustomFieldValue = new CustomFieldValue();
					itemCustomFieldValue.setName("ServiceCenterQuoteData02");
					itemCustomFieldValue.setvalue(rs1.getString("problem"));
					itemCustomFieldValues.getCustomFieldValue().add(itemCustomFieldValue);

					itemLine.setCustomFieldValues(itemCustomFieldValues);
					itemList.getItemLine().add(itemLine);
				}
				estimate.setItemList(itemList);
				add.getAccountOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCashSaleOrSalesOrderOrInvoiceOrEstimateOrCreditMemoOrReturnAuthorizationOrCustomerPaymentOrJournalEntryOrPurchaseOrderOrBillOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrTopicOrSolutionOrCaseOrEventOrTaskOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategoryOrMessage()
						.add(estimate);
				request.getAddOrAddOrUpdateOrQueryOrGet().add(add);
				smb.getHeaderOrRequestOrResponse().add(request);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				closeConnection(conn1, statement1, rs1);
			}
			return smb;
		} else if (key.equals("QA")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("false");
			AddOrUpdate addOrUpdate = new AddOrUpdate();
			Case case_ = new Case();
			case_.setHandle(rs.getString("case_handle"));
			case_.setCaseNumber(rs.getString("case_number"));

			CustomFieldValues customFieldValues = new CustomFieldValues();

			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("QuoteApprovalAcks");
			customFieldValue.setvalue("REQ_RECEIVED");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("QuoteApprovalDate");
			customFieldValue.setvalue(rs.getString("response_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("QuoteApprovalStatus");
			customFieldValue.setvalue("ACCEPTED");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("QuoteApprovalField");
			customFieldValue.setvalue("");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			case_.setCustomFieldValues(customFieldValues);

			addOrUpdate
					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
					.add(case_);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
			smb.getHeaderOrRequestOrResponse().add(request);
			return smb;
		} else if (key.equals("CA")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("false");
			AddOrUpdate addOrUpdate = new AddOrUpdate();
			Case case_ = new Case();
			case_.setHandle(rs.getString("case_handle"));
			case_.setCaseNumber(rs.getString("case_number"));

			CustomFieldValues customFieldValues = new CustomFieldValues();

			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Request Received Date");
			customFieldValue.setvalue(rs.getString("issue_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Request Received Status");
			customFieldValue.setvalue("REQ_RECEIVED");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData03");
			customFieldValue.setvalue("ACCEPTED");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData04");
			customFieldValue.setvalue("The selected unit has been processed.");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData05");
			customFieldValue.setvalue("N/A");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("ServiceCenterData06");
			customFieldValue.setvalue("N/A");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			case_.setCustomFieldValues(customFieldValues);

			addOrUpdate
					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
					.add(case_);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
			smb.getHeaderOrRequestOrResponse().add(request);
			return smb;
		} else if (key.equals("CS")) {
			Request request = new Request();
			request.setMissingFieldOverwrite("false");
			AddOrUpdate addOrUpdate = new AddOrUpdate();
			Case case_ = new Case();
			case_.setHandle(rs.getString("case_handle"));
			case_.setCaseNumber(rs.getString("case_number"));

			CustomFieldValues customFieldValues = new CustomFieldValues();

			CustomFieldValue customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Order Status");
			customFieldValue.setvalue("Shipped");
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Product Shipped");
			customFieldValue.setvalue(rs.getString("ship_date"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Shipment Tracking #");
			customFieldValue.setvalue(rs.getString("track_no"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			customFieldValue = new CustomFieldValue();
			customFieldValue.setName("Shipment Tracking - CMP Return");
			customFieldValue.setvalue(rs.getString("track_no_rtn"));
			customFieldValues.getCustomFieldValue().add(customFieldValue);

			// .......
			Connection conn1 = null;
			PreparedStatement statement1 = null;
			ResultSet rs1 = null;
			try {
				// connectiosn========================================
				conn1 = getConnection();
				StringBuffer buf = new StringBuffer();
				buf.append(" select m.apply_no,m.rma_center,m.rma_type,m.case_handle,m.case_number,m.case_entity_handle,m.keyin_date,m.track_no,m.track_no_rtn,");
				buf.append(" n.part_no,count(*)qty");
				buf.append(" from(select a.apply_no,a.rma_center,a.rma_type,");
				buf.append("  b.case_handle,b.case_number,b.case_entity_handle,");
				buf.append("  (select max(x.entry_date) from rm_header_tran x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.status='RECEIVE')keyin_date,");
				buf.append("  (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='OUT' and rownum=1)track_no,");
				buf.append("  (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='IN' and rownum=1)track_no_rtn");
				buf.append("  from rm_header a,rm_motion_header b");
				buf.append("  where a.delete_no is null");
				buf.append("  and a.apply_no=b.apply_no and a.rma_center=b.rma_center");
				buf.append("  and a.rma_center=" + rs.getString("rma_center"));
				buf.append("  and a.apply_no=" + rs.getString("apply_no"));
				buf.append(" )m,rm_item_detail n");
				buf.append(" where n.delete_no is null");
				buf.append(" and m.apply_no=n.apply_no and m.rma_center=n.rma_center");
				buf.append(" group by m.apply_no,m.rma_center,m.rma_type,m.case_handle,m.case_number,m.case_entity_handle,m.keyin_date,m.track_no,m.track_no_rtn,n.part_no");
				statement1 = conn1.prepareStatement(buf.toString());
				System.out.println("CS Detail SQL=\n" + buf);
				rs1 = statement1.executeQuery();
				int i = 0;
				while (rs1.next()) {
					i++;
					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("Part " + (i) + " - Part Consumed");
					customFieldValue.setvalue(rs1.getString("part_no"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);

					customFieldValue = new CustomFieldValue();
					customFieldValue.setName("Part " + (i) + " - Consumed QTY");
					customFieldValue.setvalue(rs1.getString("qty"));
					customFieldValues.getCustomFieldValue().add(customFieldValue);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				closeConnection(conn1, statement1, rs1);
			}

			case_.setCustomFieldValues(customFieldValues);

			addOrUpdate
					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
					.add(case_);
			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
			smb.getHeaderOrRequestOrResponse().add(request);

			return smb;
		}
		throw new Exception("invalid key !!");
	}

	private static final String folder = "D:/Workspace-4/testMotionEdiSender/file/";

	public static void main(String[] args) {
		System.out.println("main start...");
		System.setProperty("proxySet", "true");
		System.setProperty("proxyHost", "proxy8.local");
		System.setProperty("proxyPort", "80");
		System.setProperty("https.proxySet", "true");
		System.setProperty("https.proxyHost", "proxy8.local");
		System.setProperty("https.proxyPort", "80");

		// String raFile = "c://R02PEGMTC20110803.01.xml";
		// boolean raResult = marshalRA(raFile);
		// System.out.println("RA Result="+raResult);

		// String reFile = "c://R02PEUMTC20130220.06.xml";
		// boolean reResult = marshalRE(reFile);
		// System.out.println("RE Result="+reResult);

		// String shFile = "c://R02PEGMTC20121105.06.xml";
		// boolean shResult = marshalSH(shFile);
		// System.out.println("SH Result="+shResult);

		String qsFile = folder + "R02QSPEGMTC" + "20140401.97" + ".xml";
		boolean qsResult = marshalQS(qsFile);
		System.out.println("QS Result=" + qsResult);

		// String qaFile = "c://R03MTCPEG20130530.01.XML";
		// boolean qaResult = marshalQA(qaFile);
		// System.out.println("QA Result="+qaResult);

		// String caFile = "c://R02PEGCMP20110803.01.xml";
		// boolean caResult = marshalCA(caFile);
		// System.out.println("CA Result="+caResult);

		// String csFile = "c://R02PEGCMP20110803.02.xml";
		// boolean csResult = marshalCS(csFile);
		// System.out.println("CS Result="+csResult);

	}
}
